package com.icim.dao;



import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.icim.entity.Ret_cus_prd;
import com.icim.entity.Ret_cus_prd_in;

@Repository
public class RetCusPrdDao {
    @Resource
    private JdbcTemplate icimJdbcTemplate;

    public void save(Ret_cus_prd prd) {
        StringBuffer sqlBuffer = new StringBuffer("INSERT INTO RET_CUS_PRD(");
        sqlBuffer.append("LOT_NO,WO_ID,CELL_SITE,CELLID,SHEETNO,");
        sqlBuffer.append("CELLSEQ,CELLGRADE,CELLLOTNO,ARRAYLOTNO,ARRAYSITE,");
        sqlBuffer.append("LOTTYPE,CHIPGRADE,QTY,IS_WARNING,JUDGE_MODE,");
        sqlBuffer.append("ABNORMAL_CODE,FEOL_FAB_CODE,BEOL_FAB_CODE,DEFECT_CODE,PNC_CODE,");
        sqlBuffer.append("BOXID,EVT_TIMESTAMP ");
        sqlBuffer.append(") VALUES(");
        sqlBuffer.append("?,?,?,?,?,");
        sqlBuffer.append("?,?,?,?,?,");
        sqlBuffer.append("?,?,?,?,?,");
        sqlBuffer.append("?,?,?,?,?,");
        sqlBuffer.append("?,?");
        sqlBuffer.append(")");

        icimJdbcTemplate.update(
                sqlBuffer.toString(),
                new Object[] { prd.getLot_no(),prd.getWo_id(),prd.getCell_site(),prd.getCellid(),prd.getSheetno(),
                        prd.getCellseq(),prd.getCellgrade(),prd.getCelllotno(),prd.getArraylotno(),prd.getArraysite(),
                        prd.getLottype(),prd.getChipgrade(),prd.getQty(),prd.getIs_warning(),prd.getJudge_mode(),
                        prd.getAbnormal_code(),prd.getFeol_fab_code(),prd.getBeol_fab_code(),prd.getDefect_code(),prd.getPnc_code(),
                        prd.getBoxid(),prd.getEvt_timestamp() });
    };
    
    public List<Ret_cus_prd> getPrdList(String sheetId) {
      StringBuffer sqlBuffer = new StringBuffer( "SELECT LOT_NO,WO_ID,CELL_SITE,CELLID,SHEETNO,CELLSEQ,CELLGRADE,CELLLOTNO,ARRAYLOTNO,ARRAYSITE,"
          + "LOTTYPE,CHIPGRADE,QTY,IS_WARNING,JUDGE_MODE,ABNORMAL_CODE,FEOL_FAB_CODE,BEOL_FAB_CODE,DEFECT_CODE,PNC_CODE,BOXID,EVT_TIMESTAMP FROM RET_CUS_PRD WHERE CELLID = '");
      sqlBuffer.append(sheetId);
      sqlBuffer.append("'");
      return icimJdbcTemplate.query(sqlBuffer.toString(), new RowMapper<Ret_cus_prd>() {
          @Override
          public Ret_cus_prd mapRow(ResultSet rs, int rowNum) throws SQLException {
            Ret_cus_prd retCusPrd = new Ret_cus_prd();
            //LOT_NO,WO_ID,CELL_SITE,CELLID,SHEETNO,CELLSEQ,CELLGRADE,CELLLOTNO,ARRAYLOTNO,ARRAYSITE,
            //LOTTYPE,CHIPGRADE,QTY,IS_WARNING,JUDGE_MODE,ABNORMAL_CODE,FEOL_FAB_CODE,BEOL_FAB_CODE,DEFECT_CODE,PNC_CODE,BOXID
            retCusPrd.setLot_no(rs.getString("LOT_NO"));
            retCusPrd.setWo_id(rs.getString("WO_ID"));
            retCusPrd.setCell_site(rs.getString("CELL_SITE"));
            retCusPrd.setCellid(rs.getString("CELLID"));
            retCusPrd.setSheetno(rs.getString("SHEETNO"));
            retCusPrd.setCellseq(rs.getString("CELLSEQ"));
            retCusPrd.setCellgrade(rs.getString("CELLGRADE"));
            retCusPrd.setCelllotno(rs.getString("CELLLOTNO"));
            retCusPrd.setArraylotno(rs.getString("ARRAYLOTNO"));
            retCusPrd.setArraysite(rs.getString("ARRAYSITE"));
            retCusPrd.setLottype(rs.getString("LOTTYPE"));
            retCusPrd.setChipgrade(rs.getString("CHIPGRADE"));
            retCusPrd.setQty(rs.getString("QTY"));
            retCusPrd.setIs_warning(rs.getString("IS_WARNING"));
            retCusPrd.setJudge_mode(rs.getString("JUDGE_MODE"));
            retCusPrd.setAbnormal_code(rs.getString("ABNORMAL_CODE"));
            retCusPrd.setFeol_fab_code(rs.getString("FEOL_FAB_CODE"));
            retCusPrd.setBeol_fab_code(rs.getString("BEOL_FAB_CODE"));
            retCusPrd.setDefect_code(rs.getString("DEFECT_CODE"));
            retCusPrd.setPnc_code(rs.getString("PNC_CODE"));
            retCusPrd.setBoxid(rs.getString("BOXID"));
            retCusPrd.setEvt_timestamp(rs.getString("EVT_TIMESTAMP"));
            return retCusPrd;
          }
      });

  }
}
